-- ============================================================================
--		Desc			: Create table [Users]
--		Called by		:
-- ============================================================================
--		MODIFICATION LOG
-- ============================================================================
--		Date:			Author:				Description:
--		--------		--------			-----------------------------------
--		2011-3-2		alain				First Created
-- ============================================================================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Users]') AND type in (N'U'))
CREATE TABLE [dbo].[Users](
	[Id]		int PRIMARY KEY IDENTITY(1, 1) NOT NULL,
	[RoleId]	int NOT NULL,
	[Name]		varchar(100) NOT NULL,
	[Gender]	char(1),
	[Address]	varchar(200),
	[IC]		varchar(20),
	[Email]		varchar(50),
	[Phone]		varchar(50),
	[Phone2]	varchar(50),
	[Remarks]	varchar(max),
	[Dob]		datetime,
	[Status]	int NOT NULL Default 0,	
)
GO

-- ============================================================================
--		Desc			: Create table [Staffs]
--		Called by		:
-- ============================================================================
--		MODIFICATION LOG
-- ============================================================================
--		Date:			Author:				Description:
--		--------		--------			-----------------------------------
--		2011-3-2		alain				First Created
-- ============================================================================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Staffs]') AND type in (N'U'))
CREATE TABLE [dbo].[Staffs](
	[Id]		int PRIMARY KEY IDENTITY(1, 1) NOT NULL,
	[UserId]	int NOT NULL,
	[Password]	varchar(20),
	[DateJoined] datetime NOT NULL,
	[DateLeft]	 datetime,
	[IsCashier]	int NOT NULL Default 0,
)
GO

-- ============================================================================
--		Desc			: Create table [Government]
--		Called by		:
-- ============================================================================
--		MODIFICATION LOG
-- ============================================================================
--		Date:			Author:				Description:
--		--------		--------			-----------------------------------
--		2011-3-2		alain				First Created
-- ============================================================================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Government]') AND type in (N'U'))
CREATE TABLE [dbo].[Government](
	[Id]		int PRIMARY KEY IDENTITY(1, 1) NOT NULL,
	[Tax]		int NOT NULL,
	[Serv]		int NOT NULL,
	[Date]		datetime NOT NULL,
)
GO


-- ============================================================================
--		Desc			: Create table [SaleInvoices]
--		Called by		:
-- ============================================================================
--		MODIFICATION LOG
-- ============================================================================
--		Date:			Author:				Description:
--		--------		--------			-----------------------------------
--		2011-3-2		alain				First Created
-- ============================================================================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SaleInvoices]') AND type in (N'U'))
CREATE TABLE [dbo].[SaleInvoices](
	[Id]			int PRIMARY KEY IDENTITY(1, 1) NOT NULL,
	[TicketNo]		varchar(50) NOT NULL,	
	[CustomerId]	int	NOT NULL,
	[PaymentMethodId] int NOT NULL,	
	[Amount]		decimal(18,2) NOT NULL,	
	[Date]			datetime NOT NULL,
	[CreatedBy]		int NOT NULL,	
	[Status]		int NOT NULL,
	[Remarks]		varchar(max),	
)
GO

-- ============================================================================
--		Desc			: Create table [SaleInvoiceItems]
--		Called by		:
-- ============================================================================
--		MODIFICATION LOG
-- ============================================================================
--		Date:			Author:				Description:
--		--------		--------			-----------------------------------
--		2011-3-2		alain				First Created
-- ============================================================================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SaleInvoiceItems]') AND type in (N'U'))
CREATE TABLE [dbo].[SaleInvoiceItems](
	[Id]			int PRIMARY KEY IDENTITY(1, 1) NOT NULL,
	[SaleInvoiceId]	int NOT NULL,
	[StockTypeId]	int NOT NULL,
	[StockId]		int NOT NULL,
	[UserId]		int NOT NULL,
	[GovId]			int NOT NULL,
	[Qty]			int NOT NULL,
	[Amount]		numeric(18, 0) NOT NULL,
	[Status]		int NOT NULL,	
)
GO

-- ============================================================================
--		Desc			: Create table [SaleReturns]
--		Called by		:
-- ============================================================================
--		MODIFICATION LOG
-- ============================================================================
--		Date:			Author:				Description:
--		--------		--------			-----------------------------------
--		2011-3-2		alain				First Created
-- ============================================================================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SaleReturns]') AND type in (N'U'))
CREATE TABLE [dbo].[SaleReturns](
	[Id]			int PRIMARY KEY IDENTITY(1, 1) NOT NULL,
	[SaleInvoiceId]	int NOT NULL,
	[TicketNo]		varchar(50) NOT NULL,	
	[Date]			datetime,
)
GO


-- ============================================================================
--		Desc			: Create table [SaleReturnItems]
--		Called by		:
-- ============================================================================
--		MODIFICATION LOG
-- ============================================================================
--		Date:			Author:				Description:
--		--------		--------			-----------------------------------
--		2011-3-2		alain				First Created
-- ============================================================================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SaleReturnItems]') AND type in (N'U'))
CREATE TABLE [dbo].[SaleReturnItems](
	[Id] int PRIMARY KEY IDENTITY(1, 1) NOT NULL,
	[SaleReturnId]			int NOT NULL,
	[SaleInvoiceItemsId]	int NOT NULL,
	[StockId]		int	NOT NULL,
	[Qty]			int NOT NULL,	
)
GO


-- ============================================================================
--		Desc			: Create table [PurchaseOrders]
--		Called by		:
-- ============================================================================
--		MODIFICATION LOG
-- ============================================================================
--		Date:			Author:				Description:
--		--------		--------			-----------------------------------
--		2011-3-2		alain				First Created
-- ============================================================================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PurchaseOrder]') AND type in (N'U'))
CREATE TABLE [dbo].[PurchaseOrder](
	[Id] int PRIMARY KEY IDENTITY(1, 1) NOT NULL,
	[TicketNo]		varchar(50) NOT NULL,	
	[SupplierId]	int,
	[Remarks]		varchar(max),
	[Date]			datetime NOT NULL,	
)
GO


-- ============================================================================
--		Desc			: Create table [PurchaseOrderItems]
--		Called by		:
-- ============================================================================
--		MODIFICATION LOG
-- ============================================================================
--		Date:			Author:				Description:
--		--------		--------			-----------------------------------
--		2011-3-2		alain				First Created
-- ============================================================================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PurchaseOrderItems]') AND type in (N'U'))
CREATE TABLE [dbo].[PurchaseOrderItems](
	[Id]		int PRIMARY KEY IDENTITY(1, 1) NOT NULL,
	[PurchaseOrderId]	int NOT NULL,	
	[StockId]	int NOT NULL,
	[Amount]	decimal(18,2) NOT NULL,
)
GO

-- ============================================================================
--		Desc			: Create table [Stocks]
--		Called by		:
-- ============================================================================
--		MODIFICATION LOG
-- ============================================================================
--		Date:			Author:				Description:
--		--------		--------			-----------------------------------
--		2011-3-2		alain				First Created
-- ============================================================================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Stocks]') AND type in (N'U'))
CREATE TABLE [dbo].[Stocks](
	[Id] int PRIMARY KEY IDENTITY(1, 1) NOT NULL,
	[Code]			 varchar(300) NOT NULL,
	[Name]			 varchar(200) NOT NULL,
	[StockTypeId]	 int NOT NULL,
	[StockBrandId]	 int,
	[StockCategoryId] int,
	[SupplierId]	int,
	[Price]			numeric(18, 2) NOT NULL,
	[PriceVIP]		numeric(18, 2),
	[Cost]			numeric(18, 2),	
	[Remarks]		varchar(max),
	[DateCreated]	datetime,
	[DateModified]	datetime,
	[Status]		int NOT NULL,		
)
GO


-- ============================================================================
--		Desc			: Create table [StockBrands]
--		Called by		:
-- ============================================================================
--		MODIFICATION LOG
-- ============================================================================
--		Date:			Author:				Description:
--		--------		--------			-----------------------------------
--		2011-3-2		alain				First Created
-- ============================================================================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StockBrands]') AND type in (N'U'))
CREATE TABLE [StockBrands](
	[Id] int PRIMARY KEY IDENTITY(1, 1) NOT NULL,
	[Name] varchar(200) NOT NULL,
)
GO


-- ============================================================================
--		Desc			: Create table [StockCategories]
--		Called by		:
-- ============================================================================
--		MODIFICATION LOG
-- ============================================================================
--		Date:			Author:				Description:
--		--------		--------			-----------------------------------
--		2011-3-2		alain				First Created
-- ============================================================================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StockCategories]') AND type in (N'U'))
CREATE TABLE [dbo].[StockCategories](
	[Id] int PRIMARY KEY IDENTITY(1,1) NOT NULL,
	[Name]	  varchar(200) NOT NULL,
)
GO


-- ============================================================================
--		Desc			: Create table [StockDiary]
--		Called by		:
-- ============================================================================
--		MODIFICATION LOG
-- ============================================================================
--		Date:			Author:				Description:
--		--------		--------			-----------------------------------
--		2011-3-2		alain				First Created
-- ============================================================================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StockDiary]') AND type in (N'U'))
CREATE TABLE [dbo].[StockDiary](
	[Id] int PRIMARY KEY IDENTITY(1, 1) NOT NULL,
	[StockId] int NOT NULL,
	[Qty] int NOT NULL
)
GO


-- ============================================================================
--		Desc			: Create table [Payments]
--		Called by		:
-- ============================================================================
--		MODIFICATION LOG
-- ============================================================================
--		Date:			Author:				Description:
--		--------		--------			-----------------------------------
--		2011-3-2		alain				First Created
-- ============================================================================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Payments]') AND type in (N'U'))
CREATE TABLE [dbo].[Payments](
	[Id]			int PRIMARY KEY IDENTITY(1, 1) NOT NULL,
	[SaleInvoiceId]	int NOT NULL,
	[UserId]		int NOT NULL,
	[TicketNo]		varchar(50) NOT NULL,
	[DateIssue]		datetime NOT NULL,
	[Amount]		decimal(18, 2) NOT NULL,
	[PaymentMethodId] int NOT NULL,
	[Remarks]		varchar(max),	
)
GO